﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_schedule_group_detail_delete')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_schedule_group_detail_delete

GO


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-20 13:55:08
-- Database:    [scm_main-hysj2]
-- Description: 排班管理-班组排班明细人员删除
-- =============================================

CREATE PROCEDURE bd_proc_schedule_group_detail_delete
  @detailId INT,
  @ip VARCHAR(50),
  @operateId  BIGINT
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #schedule(
    columnName  VARCHAR(10),
    columnValue VARCHAR(100),
    userSerial  BIGINT,
    yearMonth VARCHAR(10)
  )

  BEGIN TRY
  	BEGIN TRANSACTION
      DECLARE @userSerial BIGINT, @shiftDate DATE, @yearMonth VARCHAR(50), @columnName VARCHAR(10)
    
      SELECT @userSerial = user_serial FROM bd_schedule_group_order_detail WHERE id = @detailId

      --排班日期
      SELECT @shiftDate = schedule_date FROM bd_schedule_group_order_detail WHERE id = @detailId

      --删除班组排班明细
      DELETE FROM bd_schedule_group_order_detail WHERE id = @detailId
    
      --取消排班
      SELECT @yearMonth = CONVERT(VARCHAR(7), @shiftDate, 120), @columnName = 'd' + CAST(DAY(@shiftDate) AS VARCHAR)
    
      INSERT INTO #schedule (columnName, columnValue, userSerial, yearMonth)
      SELECT @columnName, '', @userSerial, @yearMonth
    
      DECLARE @json VARCHAR(MAX)
      SET @json = (SELECT * FROM #schedule FOR JSON AUTO)
    
      --[{"columnName":"d8","columnValue":"","userSerial":"20004250","yearMonth":"2020-07"}]
      EXEC bd_proc_schedule_update_list @shift_info = @json
                                       ,@ip = @ip
                                       ,@operator = @operateId  		
  	COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
    END;
    RAISERROR (@errStr, 16, 1);
  END CATCH;

  DROP TABLE #schedule

  SET NOCOUNT OFF;
END